﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Finance_Outlay_Getlist_BySubjectId]
	(
		@CompanyId INT,
		@CateTwoId INT,
		@StartDate VARCHAR(10),
		@EndDate VARCHAR(10),
		@StartIndex INT,
		@EndIndex INT
	)
AS
BEGIN

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')


	;WITH list As(
   SELECT Id, 
		  CreateDate,
		  TradeDate,
		  Summary,
		  SubjectName,
		  PaymentTypeName,
		  BankName,
		  OutlayPrice,
		  AgentName,
		  EName,
		  Description,
		  (select CompanyName From Company Where Id=CompanyIdCur) As CompanyName,fo.OutlayReason
        ,1 as flag
   FROM Finance_Outlay fo WHERE fo.CompanyIdCur=@CompanyId AND fo.CateTwoId=@CateTwoId
   AND fo.CreateDate >=@sRq and  fo.CreateDate<=@eRq
    AND (CASE CateTwoId when 20  then CHARINDEX('(预存)', fo.Summary,0) else 0 end)  =0
    --查直接成本时，过滤掉从存入账户余额中产生的成本
	),
	list2 as(	
	select * from list union		
select id,createdate,Convert(varchar(10),createdate,120) as TradeDate,reason,
(select title from Finance_Subject as fs where subjectid=fs.id) as SubjectName,'' as PaymentTypeName ,'' as BankName,[money],username,username,reason,
companyname,reason,2 as flag
 from FinanceMonthTable_PZ where companyid=@CompanyId and TypeId=1  and CateTwoId=@CateTwoId and 
 CreateDate >=@sRq and  CreateDate<=@eRq
	),
	list3 as(
	select ROW_NUMBER() OVER (ORDER BY flag,CreateDate Desc)AS Row,* from list2
	
	)
	
	SELECT *  FROM list3,(select count(1) AS cid,SUM(OutlayPrice) AS tsum FROM list3 ) AS b WHERE row BETWEEN @StartIndex AND @EndIndex


END
